<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>sql_foreign_keys: common_schema documentation</title>
	<meta name="description" content="sql_foreign_keys: common_schema" />
	<meta name="keywords" content="sql_foreign_keys: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="sql_foreign_keys.html">sql_foreign_keys</a></h2>	
<h3>NAME</h3>
sql_foreign_keys: Generate create/drop foreign key constraints SQL statements
<h3>TYPE</h3>
View

<h3>DESCRIPTION</h3>
<p></p>

<p><i>sql_foreign_keys</i> provides with SQL statements to create/drop existing foreign key constraints.
</p>

<p>
Currently, foreign keys are not implemented at MySQL, but rather at the Storage Engine level. 
That is, MySQL does not manage foreign key constraints. Instead, each storage engine manages integrity within the engine, if at all.
</p>

<p>
InnoDB provides foreign key support. MyISAM/MEMORY/ARCHIVE/others do not. 3rd party engines may or may not implement foreign keys.
</p>

<p>
Unfortunately, not only does MySQL not manage the foreign keys, it also does not manage their existence, nor their definitions. 
Thus, should we ALTER and InnoDB table with foreign keys to MyISAM, all foreign key information is lost: the definition itself ceases to exist.
When ALTERing the table back to InnoDB the foreign key remains lost.
</p>

<p>
It is useful to be able to generate the SQL required to "resurrect" foreign key definitions, and <i>sql_foreign_keys</i> does just that. 
It builds upon the INFORMATION_SCHEMA views which provides the foreign key metadata to generate 
<strong>'ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ...'</strong> / <strong>'ALTER TABLE ... DROP FOREIGN KEY'</strong> statement pairs. 
</p>

<h3>STRUCTURE</h3>

<blockquote><pre>
mysql&gt; DESC common_schema.sql_foreign_keys;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| TABLE_SCHEMA     | varchar(64)  | NO   |     |         |       |
| TABLE_NAME       | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME  | varchar(64)  | NO   |     |         |       |
| drop_statement   | varchar(229) | YES  |     | NULL    |       |
| create_statement | longtext     | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
</pre></blockquote>

<h3>SYNOPSIS</h3>

<p>Columns of this view:</p>
<ul>
	<li><strong>TABLE_SCHEMA</strong>: schema of constraint's table</li>
	<li><strong>TABLE_NAME</strong>: table on which constraint is defined (this is child/dependent side of relation)</li>
	<li><strong>CONSTRAINT_NAME</strong>: name of foreign key constraint (unique within its schema)</li>
	<li><strong>drop_statement</strong>: 
		A SQL statement which drops the constraint from this table (via ALTER TABLE)
		<br/>Use with <a href="eval.html">eval()</a> to apply query.	
	</li>
	<li><strong>create_statement</strong>: 
		A SQL statement which creates this constraint (via ALTER TABLE)
		<br/>Use with <a href="eval.html">eval()</a> to apply query.	
	</li>
</ul>

<p>The SQL statements are not terminated by ';'.</p>

<h3>EXAMPLES</h3>
<p>Show foreign keys create/drop statements for `sakila`.`film_actor` (depends on `film` and `actor` tables)</p>
<blockquote><pre>mysql&gt; SELECT * FROM common_schema.sql_foreign_keys WHERE TABLE_SCHEMA='sakila' AND table_name='film_actor' \G
*************************** 1. row ***************************
    TABLE_SCHEMA: sakila
      TABLE_NAME: film_actor
 CONSTRAINT_NAME: fk_film_actor_actor
  drop_statement: ALTER TABLE `sakila`.`film_actor` DROP FOREIGN KEY `fk_film_actor_actor`
create_statement: ALTER TABLE `sakila`.`film_actor` ADD CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `sakila`.`actor` (`actor_id`) ON DELETE RESTRICT ON UPDATE CASCADE
*************************** 2. row ***************************
    TABLE_SCHEMA: sakila
      TABLE_NAME: film_actor
 CONSTRAINT_NAME: fk_film_actor_film
  drop_statement: ALTER TABLE `sakila`.`film_actor` DROP FOREIGN KEY `fk_film_actor_film`
create_statement: ALTER TABLE `sakila`.`film_actor` ADD CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `sakila`.`film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE
</pre></blockquote>

<p>Save all of sakila's foreign keys ADD CONSTRAINT statements to file:</p>
<blockquote><pre><strong>mysql&gt;</strong> SELECT create_statement FROM common_schema.sql_foreign_keys WHERE TABLE_SCHEMA='sakila' INTO OUTFILE '/tmp/create_sakila_foreign_keys.sql'

<strong>bash$</strong> cat /tmp/create_sakila_foreign_keys.sql 
ALTER TABLE `sakila`.`address` ADD CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `sakila`.`city` (`city_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`city` ADD CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `sakila`.`country` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`customer` ADD CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) REFERENCES `sakila`.`address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`customer` ADD CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) REFERENCES `sakila`.`store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`film` ADD CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `sakila`.`language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`film` ADD CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `sakila`.`language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`film_actor` ADD CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `sakila`.`actor` (`actor_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`film_actor` ADD CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `sakila`.`film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`film_category` ADD CONSTRAINT `fk_film_category_category` FOREIGN KEY (`category_id`) REFERENCES `sakila`.`category` (`category_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`film_category` ADD CONSTRAINT `fk_film_category_film` FOREIGN KEY (`film_id`) REFERENCES `sakila`.`film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`inventory` ADD CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `sakila`.`film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`inventory` ADD CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `sakila`.`store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`payment` ADD CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `sakila`.`customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`payment` ADD CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `sakila`.`rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE
ALTER TABLE `sakila`.`payment` ADD CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `sakila`.`staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`rental` ADD CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `sakila`.`customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`rental` ADD CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `sakila`.`inventory` (`inventory_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`rental` ADD CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `sakila`.`staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`staff` ADD CONSTRAINT `fk_staff_address` FOREIGN KEY (`address_id`) REFERENCES `sakila`.`address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`staff` ADD CONSTRAINT `fk_staff_store` FOREIGN KEY (`store_id`) REFERENCES `sakila`.`store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`store` ADD CONSTRAINT `fk_store_address` FOREIGN KEY (`address_id`) REFERENCES `sakila`.`address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE
ALTER TABLE `sakila`.`store` ADD CONSTRAINT `fk_store_staff` FOREIGN KEY (`manager_staff_id`) REFERENCES `sakila`.`staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE
</pre></blockquote>
<p>Note again that the SQL statements are not terminated by ';'. 
Either CONCAT() these beforehand, or use <i>sed</i>/<i>awk</i> afterwards.</p>

<h3>ENVIRONMENT</h3>
MySQL 5.1 or newer

<h3>SEE ALSO</h3>
<a href="eval.html">eval()</a>,
<a href="sql_alter_table.html">sql_alter_table</a>,
<a href="sql_range_partitions.html">sql_range_partitions</a>
<h3>AUTHOR</h3>
Shlomi Noach
				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
